﻿//-----------------------------------------------------------------------
// <copyright file="BooksDataAccessLogic.cs" company="SampleThreeTier">
//     Sample 3 tier application by Anurag.
// </copyright>
//-----------------------------------------------------------------------
namespace DataAccessLogic
{
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using DataAccessLayerContracts;
    using DataAccessLayerData;
  

    /// <summary>
    /// The book access data access layer logic
    /// </summary>
    public class BooksDataAccessLogic : IBookProcessingData
    {
        /// <summary>
        /// The connection string key
        /// </summary>
        private string connectionStringKey = "SampleThreeTierApplicationDatabase";

        /// <summary>
        /// Adds the book details.
        /// </summary>
        /// <param name="bookData">The book entity.</param>
        /// <returns>Whether the book details are added successfully</returns>
        public bool AddBookDetails(BookData bookData)
        {
            if (null == bookData)
            {
                throw new ArgumentNullException("bookData");
            }

            try
            {
                string connectionString = ConfigurationManager.ConnectionStrings[this.connectionStringKey].ConnectionString;
                using (var conn = new SqlConnection(connectionString))
                {
                    ////create the SQL command 
                    var sqlCommand = new SqlCommand(StoredProcedureNames.InsertBookDetailsStoredProcedure, conn);

                    ////specify the command is a stored procedure
                    sqlCommand.CommandType = CommandType.StoredProcedure;

                    ////add the input paramters to the command object
                    sqlCommand.Parameters.AddWithValue("@bookName", bookData.Name);
                    sqlCommand.Parameters.AddWithValue("@authorName", bookData.AuthorName);
                    sqlCommand.Parameters.AddWithValue("@genre", bookData.Genre);

                    ////open the connection and execute the non query
                    conn.Open();
                    sqlCommand.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception e)
            {
                return false;
            }
        }

        /// <summary>
        /// Gets the book details.
        /// </summary>
        /// <returns>The book data</returns>
        public List<BookData> GetBookDetails()
        {
                string connectionString = ConfigurationManager.ConnectionStrings[this.connectionStringKey].ConnectionString;
                using (var conn = new SqlConnection(connectionString))
                {
                    ////create the SQL command 
                    var sqlCommand = new SqlCommand(StoredProcedureNames.GetBookDetailsStoredProcedure, conn);

                    ////specify the command is a stored procedure
                    sqlCommand.CommandType = CommandType.StoredProcedure;

                    ////open the connection and execute the non query
                    conn.Open();
                    var reader = sqlCommand.ExecuteReader();
                    var bookDataList = new List<BookData>();
                    while (reader.Read())
                    {
                        var bookData = new BookData();
                        bookData.Id = int.Parse(reader["BookId"].ToString());
                        bookData.Name = reader["BookName"].ToString();
                        bookData.AuthorName = reader["AuthorName"].ToString();
                        bookData.Genre = reader["Genre"].ToString();
                        bookDataList.Add(bookData);
                    }

                    return bookDataList;
                }
        }

        /// <summary>
        /// Updates the book details.
        /// </summary>
        /// <param name="updatedBookDetails">The updated book details.</param>
        /// <returns>Whether the book was updated successfully</returns>
        public bool UpdateBookDetails(BookData updatedBookDetails)
        {
            if (null == updatedBookDetails)
            {
                return false;
            }

            try
            {
                string connectionString = ConfigurationManager.ConnectionStrings[this.connectionStringKey].ConnectionString;
                using (var conn = new SqlConnection(connectionString))
                {
                    ////create the SQL command 
                    var sqlCommand = new SqlCommand(StoredProcedureNames.UpdateBookDetailsStoredProcedure, conn);

                    ////specify the command is a stored procedure
                    sqlCommand.CommandType = CommandType.StoredProcedure;

                    ////add the input paramters to the command object
                    sqlCommand.Parameters.AddWithValue("@bookName", updatedBookDetails.Name);
                    sqlCommand.Parameters.AddWithValue("@authorName", updatedBookDetails.AuthorName);
                    sqlCommand.Parameters.AddWithValue("@genre", updatedBookDetails.Genre);
                    sqlCommand.Parameters.AddWithValue("@bookId", updatedBookDetails.Id);

                    ////open the connection and execute the non query
                    conn.Open();
                    sqlCommand.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception e)
            {
                return false;
            }
        }

        /// <summary>
        /// Deletes the book details.
        /// </summary>
        /// <param name="bookId">The book identifier.</param>
        /// <returns>Whether the book was deleted successfully</returns>
        public bool DeleteBookDetails(int bookId)
        {
            if (bookId >= 0)
            {
                try
                {
                    string connectionString =
                        ConfigurationManager.ConnectionStrings[this.connectionStringKey].ConnectionString;
                    using (var conn = new SqlConnection(connectionString))
                    {
                        ////create the SQL command 
                        var sqlCommand = new SqlCommand(StoredProcedureNames.DeleteBookDetailsStoredProcedure, conn);

                        ////specify the command is a stored procedure
                        sqlCommand.CommandType = CommandType.StoredProcedure;

                        ////add the input paramters to the command object
                        sqlCommand.Parameters.AddWithValue("@bookId", bookId);

                        ////open the connection and execute the non query
                        conn.Open();
                        sqlCommand.ExecuteNonQuery();
                        return true;
                    }
                }
                catch (Exception e)
                {
                    return false;
                }
            }

            return false;
        }

        /// <summary>
        /// Gets the particular book details.
        /// </summary>
        /// <param name="bookId">The book identifier.</param>
        /// <returns>The book details</returns>
        public BookData GetParticularBookDetails(int bookId)
        {   
                string connectionString = ConfigurationManager.ConnectionStrings[this.connectionStringKey].ConnectionString;
                using (var conn = new SqlConnection(connectionString))
                {
                    ////create the SQL command 
                    var sqlCommand = new SqlCommand(StoredProcedureNames.GetParticularBookDetailsStoredProcedure, conn);

                    ////specify the command is a stored procedure
                    sqlCommand.CommandType = CommandType.StoredProcedure;

                ////add the input paramters to the command object
                sqlCommand.Parameters.AddWithValue("@bookId", bookId);

                ////open the connection and execute the non query
                conn.Open();
                    var reader = sqlCommand.ExecuteReader();
                    
                    if (reader.Read())
                    {
                         var bookData = new BookData();
                        bookData.Id = int.Parse(reader["BookId"].ToString());
                        bookData.Name = reader["BookName"].ToString();
                        bookData.AuthorName = reader["AuthorName"].ToString();
                        bookData.Genre = reader["Genre"].ToString();
                        return bookData;
                     }
                    else
                    {
                        return null;
                    }                  
                }
        }
    }
}
